package dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import entity.PaperInfo;
import util.DBAccess;
import util.DateUtil;

public class PaperDao {
	
	public List<PaperInfo> listRecord(int userId) {

		return listRecord(0, Short.MAX_VALUE, userId);
	}
	public int getTotal() {
		int total = 0;
		String sql = "select count(*) from paperinfo";
		try(Connection conn = new DBAccess().getConnection(); PreparedStatement ps = conn.prepareStatement(sql);) 
		{
			ResultSet rs = ps.executeQuery();
			while(rs.next()){
				total = rs.getInt(1);
			}
			ps.close();
			conn.close();
		}catch (SQLException e) {
			e.printStackTrace();
		}
		return total;
	}

	// 显示该用户全部考试记录
	public List<PaperInfo> listRecord(int start, int count, int userId) {
		List<PaperInfo> papers = new ArrayList<PaperInfo>();
		String sql = "select * from paperinfo where uid = ? order by id desc limit ?,?";
		try (Connection conn = new DBAccess().getConnection(); PreparedStatement ps = conn.prepareStatement(sql);) {

			ps.setInt(1, userId);
			ps.setInt(2, start);
			ps.setInt(3, count);

			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				PaperInfo paper = new PaperInfo();
				String pId = rs.getString(1);
				Date createDate = DateUtil.t2d(rs.getTimestamp("createtime"));
				String userName = new UserDao().search(rs.getInt(3)).getUserName();
				int score = rs.getInt(4);
				int subject = rs.getInt(5);

				paper.setpId(pId);
				paper.setCreateTime(createDate);
				paper.setUserName(userName);
				paper.setScore(score);
				paper.setSubject(subject);

				papers.add(paper);
			}
			ps.close();
			conn.close();

		} catch (SQLException e) {
			e.printStackTrace();
		}
		return papers;
	}
	
	//根据科目查询考试记录
	public List<PaperInfo> listRecord(int userId,int subject) {

		return listRecord(0, Short.MAX_VALUE, userId,subject);
	}
	public int getTotal(int subject) {
		int total = 0;
		String sql = "select count(*) from paperinfo where subject = ?";
		try(Connection conn = new DBAccess().getConnection(); PreparedStatement ps = conn.prepareStatement(sql);) 
		{
			ps.setInt(1, subject);
			ResultSet rs = ps.executeQuery();
			while(rs.next()){
				total = rs.getInt(1);
			}
			ps.close();
			conn.close();
		}catch (SQLException e) {
			e.printStackTrace();
		}
		return total;
	}

	
	public List<PaperInfo> listRecord(int start, int count, int userId,int subject) {
		List<PaperInfo> papers = new ArrayList<PaperInfo>();
		String sql = "select * from paperinfo where uid = ? and subject = ? order by id desc limit ?,?";
		try (Connection conn = new DBAccess().getConnection(); PreparedStatement ps = conn.prepareStatement(sql);) {

			ps.setInt(1, userId);
			ps.setInt(2, subject);
			ps.setInt(3, start);
			ps.setInt(4, count);

			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				PaperInfo paper = new PaperInfo();
				String pId = rs.getString(1);
				Date createDate = DateUtil.t2d(rs.getTimestamp("createtime"));
				String userName = new UserDao().search(rs.getInt(3)).getUserName();
				int score = rs.getInt(4);
				
				

				paper.setpId(pId);
				paper.setCreateTime(createDate);
				paper.setUserName(userName);
				paper.setScore(score);
				paper.setSubject(subject);

				papers.add(paper);
			}
			ps.close();
			conn.close();

		} catch (SQLException e) {
			e.printStackTrace();
		}
		return papers;
	}

	// 查询一次考试情况
	public List<PaperInfo> queryRecord(String searchValue) {
		List<PaperInfo> papers = new ArrayList<PaperInfo>();
		String sql = "select * from paperinfo where id = ? or createtime = ?";
		try (Connection conn = new DBAccess().getConnection(); PreparedStatement ps = conn.prepareStatement(sql);) {
			ps.setString(1, searchValue);
			ps.setString(2, searchValue);
			
			ResultSet rs = ps.executeQuery();
			while(rs.next()){
			PaperInfo paper = new PaperInfo();
			paper.setpId(searchValue);
			paper.setCreateTime(DateUtil.t2d(rs.getTimestamp("createtime")));
			paper.setUserName(new UserDao().search(rs.getInt(3)).getUserName());
			paper.setScore(rs.getInt(4));
			papers.add(paper);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return papers;
	}
	
	//查询该用户近七次考试记录
	public List<Integer> listRecordNearFive(int userId,int subject) {
		List<Integer> scores = new ArrayList<Integer>();
		String sql = "select * from(select  score from paperinfo where uid = ? and subject = ? order by id desc limit 7) as tbl order by tbl.score asc";
		try (Connection conn = new DBAccess().getConnection(); PreparedStatement ps = conn.prepareStatement(sql);) {

			ps.setInt(1, userId);
			ps.setInt(2, subject);

			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				int score = rs.getInt(1);

				scores.add(score);
			}
			ps.close();
			conn.close();

		} catch (SQLException e) {
			e.printStackTrace();
		}
		return scores;
	}

	// 考试开始，新增考试记录(开始考试就往表里插入记录)
	public void addRecord(String paperId,int userId, String createDate,int subject) {
		String sql = "insert into paperinfo values(?,?,?,null,?)";
		try (Connection conn = new DBAccess().getConnection(); PreparedStatement ps = conn.prepareStatement(sql);) {
			ps.setString(1, paperId);
			ps.setString(2, createDate);
			ps.setInt(3, userId);
			ps.setInt(4, subject);
			ps.execute();
			ps.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	// 考试结束后,更新考试记录(根据考试编号更新成绩)
	public void updateScore(int score, String paperId) {
		String sql = "update paperinfo set score = ? where id = ?";
		try (Connection conn = new DBAccess().getConnection(); PreparedStatement ps = conn.prepareStatement(sql);) {
			ps.setInt(1, score);
			ps.setString(2, paperId);
		
			ps.execute();
			ps.close();
			conn.close();

		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

}
